Retrieve Rates Using Stored Procedure

When OIPA encounters a RATE or RATEARRAY math variable type, it executes one of three access methods. Accessing a stored procedure is one of those retrieval methods. The property NamedStoredProceduresRateBll.config.filePath allows a stored procedure to be used to access rates. The property identifies an XML file that is parsed for the rate group's description, the name of a stored procedure that returns a rate and the name of a stored procedure that returns a rate array. If the named stored procedure does not exist, an exception is returned to the system log. Execution will continue by using the default access to OIPA’s rate data tables. During execution, OIPA passes parameter information from the RATE or RATEARRAY configuration to the designated stored procedure and expects an appropriate return. If the appropriate result is not returned, an exception will be thrown.

Refer to the 10.2.0.0 Documentation library on OTN for a complete explanation of this property.

Modifications to the System Properties file requires a restart of the application.

Configuration Steps

Define rate table. Rate tables can be user-defined tables stored in the AsRate and AsRateGroup database tables. Alternatively, they can be custom tables stored internally on OIPA or externally and accessed by stored procedures.
Make sure the System Properties file has the stored procedure property NamedStoredProceduresRateBll.config.filePathproperly defined.
RATE and RATEARRAY can be used with stored procedures and/or extension framework.

Create the stored procedure. An example of each type is provided at the bottom of this page.
Create an XML file. An example of the XML is provided at the bottom of this page and names the file rateRetrieverProcedures.xml.
Modify or add the rateRetriever property in the System Properties document. In the example it should read as follows: #NamedStoredProceduresRateBll.config.filePath=/opt/Environments/QA-Oracle-SIT/conf/rateRetrieverProcedures.xml

Execution errors

If a stored procedure is not found when OIPA attempts to process rates and the System Properties file indicates that a stored procedure should exist, then an error will be logged and processing will continue using the default method.

If a bad parameter is passed, then an exception will be generated.

Retrieve Rate directly through Stored Procedure XML

If custom tables are set up outside of Oracle's OIPA data structure, a service request must be created to allow Oracle's integration team to help facilitate the process.

Stored Procedure Examples

Rate

Rate Stored Procedure

Rate Stored Procedure Example

RateArray

Rate Array Stored Procedure Example

Rate Retriever XML Example

Rate Retriever XML